/******************************************************************************
 * This program is a 100% Java Email Server.
 ******************************************************************************
 * Copyright (c) 2001-2011, Eric Daugherty (http://www.ericdaugherty.com)
 * All rights reserved.
 *
 * Redistribution and use in source and binary forms, with or without
 * modification, are permitted provided that the following conditions are met:
 *   * Redistributions of source code must retain the above copyright
 *     notice, this list of conditions and the following disclaimer.
 *   * Redistributions in binary form must reproduce the above copyright
 *     notice, this list of conditions and the following disclaimer in the
 *     documentation and/or other materials provided with the distribution.
 *   * Neither the name of the copyright holder nor the
 *     names of its contributors may be used to endorse or promote products
 *     derived from this software without specific prior written permission.
 *
 * THIS SOFTWARE IS PROVIDED BY THE COPYRIGHT HOLDER ''AS IS'' AND ANY
 * EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE IMPLIED
 * WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE ARE
 * DISCLAIMED. IN NO EVENT SHALL THE COPYRIGHT HOLDER BE LIABLE FOR ANY
 * DIRECT, INDIRECT, INCIDENTAL, SPECIAL, EXEMPLARY, OR CONSEQUENTIAL DAMAGES
 * (INCLUDING, BUT NOT LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES;
 * LOSS OF USE, DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND
 * ON ANY THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY, OR TORT
 * (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT OF THE USE OF THIS
 * SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE.
 *
 ******************************************************************************
 * For current versions and more information, please visit:
 * http://javaemailserver.sf.net/
 *
 * or contact the authors at:
 * java@ericdaugherty.com
 * andreaskyrmegalos@hotmail.com
 *
 ******************************************************************************
 * This program is based on the CSRMail project written by Calvin Smith.
 * http://crsemail.sourceforge.net/
 ******************************************************************************
 *
 * $Rev$
 * $Date$
 *
 ******************************************************************************/
package com.ericdaugherty.mail.server.configuration;

//Java imports
import java.io.*;
import java.net.InetAddress;
import java.sql.*;
import java.util.*;
import java.util.concurrent.ConcurrentHashMap;

//Log imports
import org.apache.commons.logging.LogFactory;
import org.apache.commons.logging.Log;

//Local imports
import com.ericdaugherty.mail.server.Mail;
import com.ericdaugherty.mail.server.configuration.backEnd.DbPersistExecutor;
import com.ericdaugherty.mail.server.configuration.cbc.CBCExecutor;
import com.ericdaugherty.mail.server.dbAccess.*;
import com.ericdaugherty.mail.server.errors.InvalidAddressException;
import com.ericdaugherty.mail.server.info.*;
import com.ericdaugherty.mail.server.info.db.*;

//Other imports
import org.apache.commons.dbcp.BasicDataSource;
import org.apache.derby.drda.NetworkServerControl;

/**
 *
 * @author Andreas Kyrmegalos
 */
public class ConfigurationManagerDb implements ConfigurationManagerBackEnd {

   /** Logger */
   private static Log log = LogFactory.getLog(ConfigurationManagerDb.class);
   private final Object realmResponseLock = new Object();
   private ConfigurationManager cm;
   private Properties sqlCommands;
   private BasicDataSource connectionPool;
   /** A Map of Users keyed by their (lowercase) full usernameDb */
   private Map<String, UserDb> users;
   /** A Map of Realms keyed by their (lowercase) full realm name */
   private Map<String, RealmDb> realms;
   private String realmsForResponse;
   private final int minimum,  maximum;
   private volatile int userCount;
   /**
    * Array of domains with the default usernameDb that the SMTP server
    * should accept mail for local delivery
    */
   private Map<Domain,User> localDomainsWithDefaultMailbox = new ConcurrentHashMap<Domain,User>();
   private Domain defaultDomain;
   private NetworkServerControl server;

   private Connection getConnection() throws SQLException {

      return connectionPool.getConnection();
   }

   public ConfigurationManagerDb(ConfigurationManager cm, final char[] usernameDb, final char[] passwordDb, int minimum, final int maximum) {

      this.cm = cm;
      this.minimum = minimum;
      this.maximum = maximum;

      //Start the Derby Network Server
      new Thread(new Runnable() {

         public void run() {
            try {

               Class.forName("com.ericdaugherty.mail.server.configuration.backEnd.DbAuthentication");
               String host = System.getProperty("derby.drda.host");
               if (host.equalsIgnoreCase("localhost")) host = null;
               server = new NetworkServerControl(InetAddress.getByName(host), Integer.valueOf(System.getProperty("derby.drda.portNumber")), new String(usernameDb), new String(passwordDb));
               server.start(null);
            } catch (Exception ex) {
               log.error(ex);
               throw new RuntimeException("Failed to start the database.");
            }
         }
      }).start();

      int inc = 1;
      do {
         try {
            Thread.sleep((int) Math.floor(Math.sqrt(inc)) * 500);
            server.ping();
            break;
         } catch (Exception e) {
         }
      } while (++inc <= 10);
      if (inc == 11) {
         throw new RuntimeException("Timed out waiting for db server to start.");
      }

      connectionPool = new BasicDataSource();
      connectionPool.setDriverClassName("org.apache.derby.jdbc.ClientDriver");
      connectionPool.setUsername(new String(usernameDb));
      connectionPool.setPassword(new String(passwordDb));
      connectionPool.setUrl("jdbc:derby://" + System.getProperty("derby.drda.host") + ":" + System.getProperty("derby.drda.portNumber") + "/JES");
      connectionPool.setConnectionProperties("create=true;");
      connectionPool.setInitialSize(2);
      connectionPool.setDefaultTransactionIsolation(Connection.TRANSACTION_READ_UNCOMMITTED);

      InputStream inputStream = null;
      inputStream = getClass().getResourceAsStream("/dbCommands.properties");
      sqlCommands = new Properties();
      try {
         sqlCommands.load(inputStream);
      } catch (IOException ex) {
         log.error(ex);
         throw new RuntimeException("Failed to load the sql command set. Aborting startup...");
      }

      users = new java.util.concurrent.ConcurrentHashMap<String, UserDb>((int) (minimum / 0.75));
      realms = new java.util.concurrent.ConcurrentHashMap<String, RealmDb>((int) (minimum / 0.75));

      Connection conn = null;
      try {

         conn = getConnection();

         //The database has been created, need to verify that the tables exist
         boolean tablesExist = conn.getMetaData().getTables(null, null, null, new String[]{"TABLE"}).next();

         if (!tablesExist) {

            if (conn != null) {
               try {
                  conn.close();
               } catch (SQLException sqle) {
               }
            }

            conn = getConnection();

            String line;
            String text;

            BufferedReader br = new BufferedReader(new InputStreamReader(getClass().getResourceAsStream("/createJESDbTables.properties")));
            conn.setAutoCommit(false);

            line = br.readLine();
            text = line.substring(0, line.length() - 1);

            Statement statement = conn.createStatement();
            if (log.isDebugEnabled()) {
               log.debug("creating table with command " + text);
            }
            statement.execute(text);
            statement.close();

            while ((line = br.readLine()) != null && line.length() > 0) {

               text = line.substring(0, line.length() - 1);

               statement = conn.createStatement();
               if (log.isDebugEnabled()) {
                  log.debug("creating table with command " + text);
               }
               statement.execute(text);
               statement.close();

            }

            conn.commit();
         }

         //Set the security permissions
         CallableStatement cs = null;
         try {
            cs = conn.prepareCall("CALL SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY('derby.database.fullAccessUsers', '" + new String(usernameDb) + "')");
            cs.execute();
         } finally {
            if (cs != null) {
               cs.close();
            }
         }
         try {
            cs = conn.prepareCall("CALL SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY('derby.database.defaultConnectionMode', 'readOnlyAccess')");
            cs.execute();
         } finally {
            if (cs != null) {
               cs.close();
            }
         }
         conn.close();

         conn = getConnection();

         conn.setAutoCommit(false);

         updateDomains(conn);

         updateRealmsForResponse(conn);

         conn.commit();

      } catch (Throwable t) {

         log.error(t);
         if (conn != null) {
            try {
               conn.rollback();
            } catch (SQLException ex) {
            }
         }
         throw new RuntimeException("Failed to initialize the db backend. Aborting startup...");
      } finally {

         for (int i = 0; i < usernameDb.length; i++) {
            usernameDb[i] = 0;
         }
         for (int i = 0; i < passwordDb.length; i++) {
            passwordDb[i] = 0;
         }
         if (conn != null) {
            try {
               conn.close();
            } catch (SQLException ex) {
            }

         }
      }
   }

   public void shutdown() {

      try {
         if (connectionPool!=null) connectionPool.close();
      } catch (Exception ex) {
         log.error(ex);
      }
      finally {
         connectionPool = null;
      }
      try {

         server.shutdown();
      } catch (Exception ex) {
         log.error(ex);
      } finally {
         server = null;
      }
   }

   public void updateRealmsForResponse() {

      Connection conn = null;
      try {

         conn = getConnection();
         conn.setAutoCommit(false);

         updateRealmsForResponse(conn);

         conn.commit();

      } catch (Throwable t) {

         if (conn != null) {
            try {
               conn.rollback();
            } catch (SQLException ex) {
               log.error(ex);
            }
         }
         log.error(t);
      } finally {
         if (conn != null) {
            try {
               conn.close();
            } catch (SQLException ex) {
               log.error(ex);
            }

         }
      }
   }

   private void updateRealmsForResponse(Connection conn) throws SQLException {

      synchronized (realmResponseLock) {
         realmsForResponse = (String) new ProcessEnvelope().executeObject(new ExecuteProcessAbstractImpl(conn, false, false, true, true) {

            @Override
            public Object executeProcessReturnObject() throws SQLException {

               psImpl = connImpl.prepareStatement(sqlCommands.getProperty("realms.load.all"));
               rsImpl = psImpl.executeQuery();
               StringBuilder challenge = new StringBuilder(1000);
               String aRealm;
               while (rsImpl.next()) {
                  aRealm = rsImpl.getString("realm_name");
                  if (aRealm.equals("null")) {
                     continue;
                  }
                  challenge.append("realm=\"").append(aRealm).append("\"");
                  challenge.append(",");
               }
               return challenge.toString();
            }
         });
      }
   }

   /* Not implemented in a db scope */
   public void restore(String backupDirectory) throws IOException {
   }

   /* Not implemented in a db scope */
   public void doBackup(String backupDirectory) throws IOException {
   }

   /* Not implemented in a db scope */
   public void doWeeklyBackup(String backupDirectory) throws IOException {
   }

   /* Not implemented in a db scope */
   public void persistUsersAndRealms() {
   }

   public void updateThroughConnection(CBCExecutor cbcExecutor) {

      log.info(cbcExecutor);

      Connection conn = null;
      try {
         conn = getConnection();
         DbPersistExecutor dbpe = new DbPersistExecutor();
         dbpe.setConnection(conn);
         dbpe.setSqlCommands(sqlCommands);
         cbcExecutor.execute(dbpe);
      } catch (SQLException sqle) {
         log.error(sqle);
      } finally {
         if (null != conn) {
            try {
               conn.close();
            } catch (SQLException ex) {
            }
         }
      }

   }

   /** This should always return false **/
   public boolean persistUserUpdate() {
      return false;
   }

   /**
    * realmName, username expected to be lowercase
    * username may contain a domain
    *
    **/
   public DomainWithPassword getRealmPassword(final String realmName, final String username) {

      RealmDb realm = (RealmDb) getRealm(realmName);
      if (log.isInfoEnabled() && realm == null) {
         log.info("Tried to load non-existent realm: " + realmName);
         return null;
      }
      //Unlike The File implementation, realm passwords are stored in a User object not the realmPass Map
      //If the username doesn't contain the domain and the realm is unspecified (its "null")
      //traverse the list of domains, get the user, then get the realm pass
      if (realmName.equals("null") && username.indexOf('@') == -1) {
         if (log.isDebugEnabled()) {
            log.debug("Checking all the available domains");
         }
         UserDb user;
         Domain domain;
         String password;
         Iterator<Domain> iter = localDomainsWithDefaultMailbox.keySet().iterator();
         while (iter.hasNext()) {

            domain = iter.next();
            try {
               user = (UserDb) getUser(new EmailAddress(username + '@' + domain.getDomainName()));
               if (user != null) {
                  password = user.getRealmPass(realm);
                  if (password != null) {
                     return new DomainWithPassword(domain, password.toCharArray());
                  }
               }
            } catch (InvalidAddressException iae) {
               continue;
            }
         }
         return null;
      } else {
         char[] password = users.get(username).getRealmPass(realm).toCharArray();
         if (password == null) {
            return null;
         }
         return new DomainWithPassword(null, password);
      }
   }

   public void loadUsersAndRealms() {

      //Load the minimum number of users
      Connection conn = null;
      try {

         conn = getConnection();
         new ProcessEnvelope().executeNull(new ExecuteProcessAbstractImpl(conn, false, false, true, true) {

            @Override
            public void executeProcessReturnNull() throws SQLException {
               psImpl = connImpl.prepareStatement(sqlCommands.getProperty("users.load"));
               psImpl.setMaxRows(minimum);
               rsImpl = psImpl.executeQuery();
               String fullUsername;
               String correctedUsername;
               while (rsImpl.next()) {

                  fullUsername = rsImpl.getString("username") + '@' + rsImpl.getString("domain_name");
                  correctedUsername = rsImpl.getString("username_lower_case") + '@' + rsImpl.getString("domain_name_lower_case");
                  try {
                     users.put(correctedUsername, loadUser(connImpl, rsImpl.getInt("user_id"), fullUsername, rsImpl.getString("password"), rsImpl.getString("salt")));
                  } catch (InvalidAddressException e) {
                     throw new RuntimeException("Error Loading user!  Unable to continue Operation. Improper syntax for " + fullUsername);
                  }
               }

            }
         });

         if (users.isEmpty() && !(Mail.testing || cm.isLocalTestingMode())) {
            log.warn("No users registered!!!");
         }

         //Only load the realms that correspond to the loaded users
         new ProcessEnvelope().executeNull(new ExecuteProcessAbstractImpl(conn, false, false, true, true) {

            @Override
            public void executeProcessReturnNull() throws SQLException {

               String fullRealmName;
               String correctedRealmName;
               String domain;
               UserDb user;
               RealmDb realm;
               psImpl = connImpl.prepareStatement(sqlCommands.getProperty("realms.load"));
               Iterator<UserDb> iter = users.values().iterator();

               while (iter.hasNext()) {

                  user = iter.next();
                  psImpl.setInt(1, user.getUserId());
                  rsImpl = psImpl.executeQuery();
                  while (rsImpl.next()) {

                     fullRealmName = rsImpl.getString("realm_name");
                     correctedRealmName = rsImpl.getString("realm_name_lower_case");
                     realm = loadRealm(fullRealmName, rsImpl.getInt("realm_id"));
                     user.addRealmPass(realm, rsImpl.getString("password"));
                     if (realms.containsKey(correctedRealmName)) {
                        continue;
                     }
                     domain = correctedRealmName.substring(correctedRealmName.indexOf('@') + 1);
                     if (!localDomainsWithDefaultMailbox.containsKey(new Domain(domain))) {
                        continue;
                     }
                     realms.put(correctedRealmName, realm);
                  }
               }
               if (log.isInfoEnabled()) {
                  log.info("Loaded " + realms.size() + " realms from the database");
               }
            }
         });


      } catch (SQLException sqle) {

         log.error(sqle);
      } finally {
         if (conn != null) {
            try {
               conn.close();
            } catch (SQLException sqle) {
               log.error(sqle);
            }
         }
      }


   }

   /**
    * Creates a new User instance for the specified usernameDb
    * using the specified properties.
    *
    * @param fullAddress full usernameDb (me@mydomain.com)
    * @param properties the properties that contain the user parameters.
    * @return a new User instance.
    */
   private UserDb loadUser(Connection conn, final int userId, String fullAddress, String password, String salt) throws InvalidAddressException, SQLException {

      EmailAddress address = new EmailAddress(fullAddress);
      final UserDb user = new UserDb(address, userId);
      // Load the passwordDb
      user.setPassword(password);
      user.setSalt(salt);

      new ProcessEnvelope().executeNull(new ExecuteProcessAbstractImpl(conn, false, false, true, true) {

         @Override
         public void executeProcessReturnNull() throws SQLException {
            psImpl = connImpl.prepareStatement(sqlCommands.getProperty("userForwardAddresses.load"));
            psImpl.setInt(1, userId);
            rsImpl = psImpl.executeQuery();
            List<EmailAddress> addressList = new ArrayList<EmailAddress>(10);
            String forwardAddress;
            while (rsImpl.next()) {
               forwardAddress = rsImpl.getString("forward_address");
               try {
                  addressList.add(new EmailAddress(forwardAddress));
               } catch (InvalidAddressException e) {
                  log.warn("Forward address: " + forwardAddress + " for user " + user.getFullUsername() + " is invalid and will be ignored.");
               }
            }
            EmailAddress[] emailAddresses = new EmailAddress[addressList.size()];
            emailAddresses = (EmailAddress[]) addressList.toArray(emailAddresses);

            if (log.isDebugEnabled()) {
               log.debug(emailAddresses.length + " forward addresses load for user: " + user.getFullUsername());
            }
            user.setForwardAddresses(emailAddresses);
         }
      });

      return user;
   }

   /**
    * Creates a new Realm instance for the specified Realm name
    * Users are not added since it is not needed in a db context
    *
    * @param fullRealmName full Realm name (realmName@mydomain.com)
    * @return a new Realm instance.
    */
   public RealmDb loadRealm(String fullRealmName, int realmId) {

      RealmDb realm = new RealmDb(fullRealmName, realmId);
      return realm;
   }

   /* Not implemented in a db scope */
   public void updateUsersAndRealmPasswords() {
   }

   public String getRealmsForResponse() {

      synchronized (realmResponseLock) {
         return realmsForResponse;
      }
   }

   /*
    * The method parameters are ignored
    *
    */
   public void updateDomains(String domains, String defaultMailboxes) {

      Connection conn = null;
      try {

         conn = getConnection();
         conn.setAutoCommit(false);

         updateDomains(conn);

         conn.commit();

      } catch (Throwable t) {

         if (conn != null) {
            try {
               conn.rollback();
            } catch (SQLException ex) {
               log.error(ex);
            }
         }
         log.error(t);
      } finally {
         if (conn != null) {
            try {
               conn.close();
            } catch (SQLException ex) {
               log.error(ex);
            }

         }
      }
   }

   private void updateDomains(Connection conn) throws SQLException {

      //Load the domains
      final int defaultDomainId = (Integer) new ProcessEnvelope().executeObject(new ExecuteProcessAbstractImpl(conn, false, false, true, true) {

         @Override
         public Object executeProcessReturnObject() throws SQLException {

            psImpl = connImpl.prepareStatement(sqlCommands.getProperty("domains.getDefaultDomainId"));
            rsImpl = psImpl.executeQuery();
            if (rsImpl.next()) {
               return rsImpl.getInt(1);
            }
            return -1;
         }
      });

      new ProcessEnvelope().executeNull(new ExecuteProcessAbstractImpl(conn, false, false, true, true) {

         @Override
         public void executeProcessReturnNull() throws SQLException {

            psImpl = connImpl.prepareStatement(sqlCommands.getProperty("domains.load"));
            rsImpl = psImpl.executeQuery();
            int domainId;
            String dUser;
            EmailAddress address;
            UserDb user;
            while (rsImpl.next()) {

               dUser = rsImpl.getString("username");
               address = null;
               user = null;
               try {
                  if (dUser != null) {
                     dUser = dUser + "@" + rsImpl.getString("domain_name");
                     address = new EmailAddress(dUser);
                     user = (UserDb) getUser(address);
                  }
                  domainId = rsImpl.getInt("domain_id");
                  if (domainId == defaultDomainId) {
                     defaultDomain = new DomainDb(rsImpl.getString("domain_name_lower_case"), domainId);
                  }
                  localDomainsWithDefaultMailbox.put(new DomainDb(rsImpl.getString("domain_name_lower_case"), domainId), user == null ? new User(new EmailAddress()) : user);
               } catch (InvalidAddressException iae) {
                  throw new RuntimeException("Invalid username " + dUser);
               }
            }

         }
      });

      if (localDomainsWithDefaultMailbox.isEmpty()) {
         log.warn("No local domains registered with JES.");
      }
   }

   public Domain getDomain(int domainId) {

      Iterator<Domain> iter = localDomainsWithDefaultMailbox.keySet().iterator();
      DomainDb domain;
      while (iter.hasNext()) {
         domain = (DomainDb) iter.next();
         if (domain.getDomainId() == domainId) {
            return domain;
         }
      }
      return null;
   }

   public boolean isLocalDomain(Domain domain) {

      return localDomainsWithDefaultMailbox.keySet().contains(domain);
   }

   public Domain getDefaultDomain() {

      return defaultDomain;
   }

   public void updateDefaultDomain() {

      defaultDomain = new DomainDb(null, -1);
      Connection conn = null;
      try {

         conn = getConnection();

         new ProcessEnvelope().executeNull(new ExecuteProcessAbstractImpl(conn, false, false, true, true) {

            @Override
            public void executeProcessReturnNull() throws SQLException {

               psImpl = connImpl.prepareStatement(sqlCommands.getProperty("domains.getDefaultDomain"));
               rsImpl = psImpl.executeQuery();
               if (rsImpl.next()) {

                  defaultDomain = new DomainDb(rsImpl.getString("domain_name_lower_case"), rsImpl.getInt("domain_id"));
               }

            }
         });
      } catch (SQLException sqle) {
         log.error(sqle);
      } finally {
         if (null != conn) {
            try {
               conn.close();
            } catch (SQLException ex) {
               log.error(ex);
            }
         }
      }
   }

   public EmailAddress getDefaultMailbox(Domain domain) {

      return localDomainsWithDefaultMailbox.get(domain).getEmailAddress();
   }

   public EmailAddress getDefaultMailbox(int domainId) {

      Iterator<Domain> iter = localDomainsWithDefaultMailbox.keySet().iterator();
      DomainDb domain;
      while (iter.hasNext()) {

         domain = (DomainDb) iter.next();
         if (domain.getDomainId() == domainId) {
            return localDomainsWithDefaultMailbox.get(domain).getEmailAddress();
         }
      }
      return null;
   }

   public User getUser(final EmailAddress address) {

      final String fullUsername = address.getAddress();
      final String correctedUsername = fullUsername.toLowerCase(cm.englishLocale);

      if (userCount == maximum) {

         realms.clear();
         users.clear();
         loadUsersAndRealms();
      }
      
      UserDb user = users.get(correctedUsername);

      if (user == null) {

         Connection conn = null;
         try {

            conn = getConnection();
            user = (UserDb) new ProcessEnvelope().executeObject(new ExecuteProcessAbstractImpl(conn, false, false, true, true) {

               @Override
               public Object executeProcessReturnObject() throws SQLException {

                  psImpl = connImpl.prepareStatement(sqlCommands.getProperty("users.load.single"));
                  psImpl.setString(1, address.getUsername().toLowerCase(cm.englishLocale));
                  psImpl.setString(2, address.getDomain().toLowerCase(cm.englishLocale));
                  rsImpl = psImpl.executeQuery();
                  UserDb user;
                  if (rsImpl.next()) {

                     try {
                        user = loadUser(connImpl, rsImpl.getInt("user_id"), fullUsername, rsImpl.getString("password"), rsImpl.getString("salt"));
                        users.put(correctedUsername, user);
                        userCount = users.size();
                     } catch (InvalidAddressException e) {
                        throw new RuntimeException("Error Loading user!  Unable to continue Operation. Improper syntax for " + fullUsername);
                     }
                     return user;
                  }
                  return null;
               }
            });

            //Load all the realms associated with this user
            if (user != null) {

               final UserDb finalUser = user;

               new ProcessEnvelope().executeObject(new ExecuteProcessAbstractImpl(conn, false, false, true, true) {

                  @Override
                  public void executeProcessReturnNull() throws SQLException {

                     String fullRealmName;
                     String correctedRealmName;
                     String domain;
                     RealmDb realm;
                     psImpl = connImpl.prepareStatement(sqlCommands.getProperty("realms.load"));
                     psImpl.setInt(1, finalUser.getUserId());
                     rsImpl = psImpl.executeQuery();
                     while (rsImpl.next()) {

                        fullRealmName = rsImpl.getString("realm_name");
                        correctedRealmName = rsImpl.getString("realm_name_lower_case");
                        realm = loadRealm(fullRealmName, rsImpl.getInt("realm_id"));
                        finalUser.addRealmPass(realm, rsImpl.getString("password"));
                        if (realms.containsKey(correctedRealmName)) {
                           continue;
                        }
                        domain = correctedRealmName.substring(correctedRealmName.indexOf('@') + 1);
                        if (!localDomainsWithDefaultMailbox.containsKey(new Domain(domain))) {
                           continue;
                        }
                        realms.put(correctedRealmName, realm);
                     }
                     if (log.isInfoEnabled()) {
                        log.info("Loaded " + realms.size() + " realms from the database");
                     }
                  }
               });
            }

         } catch (SQLException sqle) {

            log.error(sqle);
         } finally {
            if (conn != null) {
               try {
                  conn.close();
               } catch (SQLException sqle) {
                  log.error(sqle);
               }
            }
         }

      }

      return user;
   }

   public void checkIfDefaultMailboxDeleted(List<Integer> userIds) {

      List<Domain> deletedDefaultMailBoxes = new ArrayList<Domain>();
      Iterator<Domain> iter = localDomainsWithDefaultMailbox.keySet().iterator();
      Iterator<Integer> iter2;
      UserDb defaultMailBox;
      Domain domain;
      int userId;
      while (iter.hasNext()) {
         domain = iter.next();
         defaultMailBox = (UserDb) localDomainsWithDefaultMailbox.get(domain);
         iter2 = userIds.iterator();
         while (iter2.hasNext()) {
            userId = iter2.next();
            if (userId == defaultMailBox.getUserId()) {
               deletedDefaultMailBoxes.add(domain);
            }
         }
      }
      if (!deletedDefaultMailBoxes.isEmpty()) {

         localDomainsWithDefaultMailbox.clear();
         updateDomains(null, null);
      }
   }

   /** realmName expected to be lower case **/
   public Realm getRealm(final String realmName) {

      RealmDb realm = realms.get(realmName);

      //Perhaps it has not been loaded. Load it now
      if (realm == null) {

         Connection conn = null;
         try {

            conn = getConnection();
            realm = (RealmDb) new ProcessEnvelope().executeObject(new ExecuteProcessAbstractImpl(conn, false, false, true, true) {

               @Override
               public Object executeProcessReturnObject() throws SQLException {

                  String fullRealmName;
                  String correctedRealmName;
                  psImpl = connImpl.prepareStatement(sqlCommands.getProperty("realms.load.single"));
                  psImpl.setString(1, realmName);
                  rsImpl = psImpl.executeQuery();
                  if (rsImpl.next()) {

                     fullRealmName = rsImpl.getString("realm_name");
                     correctedRealmName = rsImpl.getString("realm_name_lower_case");
                     RealmDb realm = loadRealm(fullRealmName, rsImpl.getInt("realm_id"));
                     realms.put(correctedRealmName, realm);
                     return realm;
                  }
                  return null;
               }
            });


         } catch (SQLException sqle) {

            log.error(sqle);
         } finally {
            if (conn != null) {
               try {
                  conn.close();
               } catch (SQLException sqle) {
                  log.error(sqle);
               }
            }
         }
      }

      return realm;
   }

   public boolean isUserARealmMember(final Realm realm, final String usernameLowerCase) {

      Connection conn = null;
      try {

         conn = getConnection();
         return (Boolean) new ProcessEnvelope().executeObject(new ExecuteProcessAbstractImpl(conn, false, false, true, true) {

            @Override
            public Object executeProcessReturnObject() throws SQLException {

               psImpl = connImpl.prepareStatement(sqlCommands.getProperty("realm.isUserAMember"));
               psImpl.setInt(1, ((RealmDb) realm).getRealmId());
               psImpl.setString(2, usernameLowerCase);
               rsImpl = psImpl.executeQuery();
               if (rsImpl.next()) {
                  return rsImpl.getInt(1) > 0;
               }
               return false;
            }
         });
      } catch (SQLException sqle) {
         log.error(sqle);
         return false;
      }
   }

   public void removeUser(int userId) {

      Iterator<UserDb> iter = users.values().iterator();
      UserDb user;
      while (iter.hasNext()) {
         user = iter.next();
         if (user.getUserId() == userId) {
            users.remove(user.getUsername().toLowerCase(Locale.ENGLISH));
            break;
         }
      }
   }

   public void removeUsers(List<Integer> userIds) {

      Iterator<Integer> iter = userIds.iterator();
      while (iter.hasNext()) {
         removeUser(iter.next());
      }
   }

   public void removeRealm(int realmId) {

      Iterator<RealmDb> iter = realms.values().iterator();
      RealmDb realm;
      while (iter.hasNext()) {
         realm = iter.next();
         if (realm.getRealmId() == realmId) {
            realms.remove(realm.getFullRealmName().toLowerCase(Locale.ENGLISH));
            break;
         }
      }
   }
}
